package com.wxy.ideaplugin.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sun.istack.Nullable;
import com.wxy.ideaplugin.ui.AiCodeUi;
import lombok.SneakyThrows;
import okhttp3.Response;
import okhttp3.sse.EventSource;
import okhttp3.sse.EventSourceListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.swing.*;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.*;

public class SqlGenThread extends Thread{
    private static final Logger log = LoggerFactory.getLogger(SqlGenThread.class);
    private JSONObject groupMenuJson;
    private String softwareName;
    private String tablePrefix;
    private JTextArea aiLog_textArea;
    private JTextArea aichat_textArea;
    private List<Map<String,String>> tableInfoList;
    private JScrollBar aiLog_scrollBar;
    private JScrollBar aichat_scrollBar;
    private JScrollPane aiLog_scrollPane;
    private JScrollPane aichat_scrollPane;

    public SqlGenThread(JSONObject groupMenuJson, String softwareName, String tablePrefix, JTextArea aiLog_textArea, JTextArea aichat_textArea, List<Map<String,String>> tableInfoList) {
        this.groupMenuJson = groupMenuJson;
        this.softwareName = softwareName;
        this.tablePrefix = tablePrefix;
        this.aiLog_textArea = aiLog_textArea;
        this.aichat_textArea = aichat_textArea;
        this.tableInfoList = tableInfoList;
        //滚动尾部
        this.aiLog_scrollPane = (JScrollPane)aiLog_textArea.getParent().getParent();
        this.aiLog_scrollBar=aiLog_scrollPane.getVerticalScrollBar();
        this.aichat_scrollPane = (JScrollPane)aichat_textArea.getParent().getParent();
        this.aichat_scrollBar=aichat_scrollPane.getVerticalScrollBar();
    }

    public void run() {
        aiLog_textArea.setText("");
        aichat_textArea.setText("");
        for (String key : groupMenuJson.keySet()) {
            JSONArray tableNameZhList = groupMenuJson.getJSONArray(key); //["库存表", "库存变动记录表", "批次管理表"]
            for (int i = 0; i < tableNameZhList.size(); i++) {
                String tableNameZh = (String) tableNameZhList.get(i);
                String q = "你是一个数据库设计师，给系统：" + softwareName + "，表名：" + tableNameZh + "；编写数据库表的sql创建脚本，数据库是mysql，只显示一张表，" +
                        "表名称要求：表名称前缀用" + tablePrefix + "，表名称要求是英语，表名是单数，不要带s的复数，表名不以s结尾，" +
                        "字段要求：字段名称要求是英语，字段id是表主键，id是bigint类型，不用创建时间和更新时间字段，不用foreign key字段，字段不用enum枚举类型，" +
                        "sql语句要求：create语句中字段加comment注解，表加comment注解，所有comment注解用中文；sql语句中不显示ENGINE、CHARSET、COLLATE，" +
                        "编写insert批量插入sql脚本，批量插入5条模拟数据，模拟数据用中文，插入语句中不得有comment注解，保证脚本可以正常执行；" +
                        "create创建脚本和insert插入脚本分成两段sql脚本显示，每段脚本的开头用```sql，结束符用```";

                String r1 = "";
                String querySql = "select * from tai_chatai where domain_name = ? and (resp is not null and resp!='')";
                List paramList = new ArrayList();
                //paramList.add(q);
                paramList.add(softwareName+"##"+tableNameZh);
                List<Map<String, Object>> list = JdbcUtils.executeQuery(querySql, paramList);
                if (!list.isEmpty()) {
                    Map<String, Object> map = list.get(list.size() - 1);
                    r1 = (String) map.get("resp");
                    //换表前缀
                    String table_prefix_old = (String) map.get("table_prefix");
                    r1 = r1.replaceAll(" "+table_prefix_old, " "+tablePrefix);
                    r1 = r1.replaceAll(" `"+table_prefix_old, " `"+tablePrefix);
                    doAiLogTextArea(tableNameZh, r1);
                    continue;
                }
                //chat api
                doAiChat(q, tableNameZh);
            }
        }
        //滚动尾部
        try {
            Thread.sleep(100);
        } catch (InterruptedException ex) {
            ex.printStackTrace();
        }
        aiLog_textArea.append("#------- 脚本生成完成 -------;\n\n\n");
        this.aiLog_scrollBar.setValue(aiLog_scrollBar.getMaximum());
        aiLog_scrollBar.repaint();
    }

    private void doAiChat(String q, String tableNameZh) {
        CountDownLatch latch = new CountDownLatch(1);
        //api
        HttpUtils.getInstance().chatApiV2(q, new EventSourceListener() {
            StringBuilder resultSb = new StringBuilder();
            @Override
            public void onOpen(EventSource eventSource, Response response) {
                log.info("----onOpen");
                aichat_textArea.setText("");
                super.onOpen(eventSource, response);
            }
            @Override
            public void onEvent(EventSource eventSource, @Nullable String id, @Nullable String type, String data) {
                log.info("----onEvent");
                JSONObject dataJo = JSONObject.parseObject(data);
                boolean is_end = dataJo.getBoolean("is_end");
                String result = dataJo.getString("result");
                aichat_textArea.append(result);
                aichat_scrollBar.setValue(aichat_scrollBar.getMaximum());//滚动尾部
                aichat_scrollBar.repaint();
                resultSb.append(result);
                super.onEvent(eventSource, id, type, data);
            }
            @Override
            public void onClosed(EventSource eventSource) {
                log.info("----onClosed");
                log.info(resultSb.toString());
                // 查询出的结果，进行缓存
                String insertSql = "insert into tai_chatai(id, domain_name, q, table_prefix, resp) values(?,?,?,?,?)";
                List paramList = new ArrayList();
                paramList.add(IdUtils.genId());
                paramList.add(softwareName+"##"+tableNameZh);
                paramList.add(q);
                paramList.add(tablePrefix);
                paramList.add(resultSb.toString());
                JdbcUtils.executeInsert(insertSql, paramList);
                //parse data
                String result2 = resultSb.toString();
                doAiLogTextArea(tableNameZh, result2);
                //
                latch.countDown();
                super.onClosed(eventSource);
            }
            @Override
            public void onFailure(EventSource eventSource, @Nullable Throwable t, @Nullable Response response) {
                log.info("----onFailure");
                latch.countDown();
                super.onFailure(eventSource, t, response);
            }
        });
        try {
            latch.await(); // 阻塞直到CountDownLatch减到0
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }


    private void doAiLogTextArea(String tableNameZh, String r1) {
        //tableNameZh
        aiLog_textArea.append("#");
        aiLog_textArea.append(tableNameZh);
        aiLog_textArea.append(";\n");
        //create sql
        String sql_create = parseCreateSql(r1);
        String tableName = parseTableName(sql_create);
        //tableInfoMap
        Map<String,String> tableInfoMap = new HashMap<>();
        tableInfoMap.put("tableNameZh", tableNameZh);
        tableInfoMap.put("tableName", tableName);
        tableInfoList.add(tableInfoMap);
        //drop
        String sql_drop = "drop table if exists "+tableName+";";
        aiLog_textArea.append(sql_drop);
        aiLog_textArea.append("\n");
        aiLog_scrollBar.setValue(aiLog_scrollBar.getMaximum());//滚动尾部
        aiLog_scrollBar.repaint();
        aiLog_textArea.append(sql_create);
        aiLog_textArea.append("\n");
        aiLog_scrollBar.setValue(aiLog_scrollBar.getMaximum());//滚动尾部
        aiLog_scrollBar.repaint();
        aiLog_scrollBar.repaint();
        //alter table add default column
        String sql_newColumns = genNewColumn(tableName);
        aiLog_textArea.append(sql_newColumns);
        aiLog_scrollBar.setValue(aiLog_scrollBar.getMaximum());//滚动尾部
        aiLog_scrollBar.repaint();
        //insert sql
        aiLog_textArea.append("#插入模拟数据;\n");
        String sql_insert=parseInsertSql(r1);
        aiLog_textArea.append(sql_insert);
        aiLog_textArea.append("\n\n");
        aiLog_scrollBar.setValue(aiLog_scrollBar.getMaximum());//滚动尾部
        aiLog_scrollBar.repaint();
    }

    /*
        defaultColumnList.add("delete_flag");
        defaultColumnList.add("creator");
        defaultColumnList.add("creator_id");
        defaultColumnList.add("create_time");
        defaultColumnList.add("updator");
        defaultColumnList.add("updator_id");
        defaultColumnList.add("update_time");
        defaultColumnList.add("company_id");
        defaultColumnList.add("version_no");
     */
    public static String genNewColumn(String tableName) {
        StringBuilder sb = new StringBuilder();
        sb.append("alter table #tablename# add column delete_flag int not null default 0 comment '是否删除;模板：逻辑删除标志：0:false,有效数据；1:true，删除数据' ;\n");
        sb.append("alter table #tablename# add column `version` int not null default 0 comment '版本号;乐观锁版本号' ;\n");
        //sb.append("alter table #tablename# add column version_no int not null default 0 comment '版本号;乐观锁版本号' ;\n");
        sb.append("alter table #tablename# add column create_time timestamp not null default CURRENT_TIMESTAMP comment '创建时间;' ;\n");
        sb.append("alter table #tablename# add column creator varchar(100) null comment '创建人姓名;' ;\n");
        sb.append("alter table #tablename# add column creator_id bigint NOT NULL DEFAULT '0' comment '创建人ID;' ;\n");
        sb.append("alter table #tablename# add column update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间;' ;\n");
        sb.append("alter table #tablename# add column updator varchar(100) null comment '更新人;' ;\n");
        sb.append("alter table #tablename# add column updator_id bigint NOT NULL DEFAULT '0' COMMENT '更新人ID;' ;\n");
        sb.append("alter table #tablename# add column company_id bigint NOT NULL DEFAULT '0' COMMENT '公司ID;模板：创建人组织或公司ID，数据权限控制用，主要应用SAAS平台' ;\n");
        String sql = sb.toString().toLowerCase(Locale.ROOT);
        sql = sql.replaceAll("#tablename#", tableName);
        return sql;
    }


    /*
    r1 = "以下是使用 `bigint` 类型作为主键的 WMS 商品表（tw1_products）的 SQL 创建脚本示例：<br/><br/>
    ```sql<br/>CREATE TABLE `tw1_products` (<br/>  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',<br/>  `product_code`varchar(255) NOT NULL COMMENT '商品编码',<br/>  `product_name` varchar(255) NOT NULL COMMENT '商品名称',<br/>  `category` varchar(255) DEFAULT NULL COMMENT '类别',<br/>  `description` text DEFAULT NULL COMMENT '描述',<br/>  `unit` varchar(50) DEFAULT NULL COMMENT '计量单位',<br/>  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',<br/>  PRIMARY KEY (`id`)<br/>) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表'; <br/>```<br/><br/>接下来，为 `tw1_products` 表插入 5 条模拟数据：<br/><br/>```sql<br/>INSERT INTO `tw1_products` (`product_code`, `product_name`, `category`, `description`, `unit`, `price`)<br/>VALUES<br/>('P0001', 'Product 1', 'Category A', 'Product 1 description', 'pcs', 10.00),<br/>('P0002', 'Product 2', 'Category A', 'Product 2 description', 'pcs', 20.00),<br/>('P0003', 'Product 3', 'Category B', 'Product 3 description', 'pcs', 30.00),<br/>('P0004', 'Product 4','Category B', 'Product 4 description', 'pcs', 40.00),<br/>('P0005', 'Product 5', 'Category C', 'Product 5 description','pcs', 50.00);<br/>```<br/><br/>请注意，您可能需要根据实际业务需求调整表结构，例如调整字段类型、长度、是否为空以及视情况添加其他字段等。"
    */
    public static String parseCreateSql(String str) {
        String r = str;//.replaceAll("<br/>","");
        r = r.toLowerCase();
        int startIndex = 0;
        if (str.contains("create table `")) {
            startIndex = r.indexOf("create table `");
        }else {
            startIndex = r.indexOf("create table ");
        }
        int endIndex=r.indexOf(";", startIndex+1)+1;
        r = r.substring(startIndex, endIndex);
        r = r.trim();
        return r;
    }

    public static String parseTableName(String createSqlStr) {
        createSqlStr = createSqlStr.toLowerCase();
        int startIndex = createSqlStr.indexOf("create table ")+12;
        int endIndex=createSqlStr.indexOf("(", startIndex);
        String tableName = createSqlStr.substring(startIndex, endIndex);
        tableName = tableName.replaceAll("`","");
        tableName = tableName.trim();
        return tableName;
    }

    public static void main(String[] args) {
        String sql = "create table `toa_shared_calendar` (\n" +
                "  `id` bigint not null auto_increment comment '主键id',\n" +
                "  `event_title` varchar(255) not null comment '事件标题',\n" +
                "  `event_start_time` datetime not null comment '事件开始时间',\n" +
                "  `event_end_time` datetime not null comment '事件结束时间',\n" +
                "  `organizer` varchar(255) not null comment '组织者',\n" +
                "  `participants` text comment '参与者列表',\n" +
                "  `event_location` varchar(255) comment '事件地点',\n" +
                "  `event_description` text comment '事件描述',\n" +
                "  `is_all_day` tinyint(1) not null default '0' comment '是否为全天事件（0-否，1-是）',\n" +
                "  `reminder_time` datetime comment '提醒时间',\n" +
                "  primary key (`id`)\n" +
                ") comment='共享日程表';";
        System.out.println(parseTableName(sql));
        //System.out.println(parseCreateSql(sql));

    }

    public static String parseInsertSql(String str) {
        String r = str;//.replaceAll("<br/>","");
        r = r.toLowerCase();
        int startIndex = 0;
        if (str.contains("insert into `")) {
            startIndex = r.indexOf("insert into `");
        }else {
            startIndex = r.indexOf("insert into ");
        }
        int endIndex=r.indexOf("```", startIndex+1);
        r = r.substring(startIndex, endIndex);
        r = r.trim();
        return r;
    }



}
